package com.jk.util.autotest.util;

import com.jk.util.autotest.model.InterfaceInfo;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;

public class ExcelInputUtil {

    private static Workbook getReadWorkBookType(String filePath) throws Exception {
        FileInputStream is = null;
        try {
            is = new FileInputStream(filePath);
            if (filePath.toLowerCase().endsWith("xlsx")) {
                return new XSSFWorkbook(is);
            }
            else if (filePath.toLowerCase().endsWith("xls")) {
                return new HSSFWorkbook(is);
            }
            else {
                throw new Exception("excel格式文件错误");
            }
        } catch (Exception e) {
            throw e;
        } finally {
            IOUtils.closeQuietly(is);
        }
    }

    /**
     * 解析excel
     * @param sourceFilePath
     * @return
     * @throws Exception
     */
    public static List<InterfaceInfo> readExcel(String sourceFilePath) throws Exception {
        Workbook workbook = null;
        try {
            workbook = getReadWorkBookType(sourceFilePath);
            List<InterfaceInfo> contents = new ArrayList();
            //获取第一个sheet
            Sheet sheet = workbook.getSheetAt(0);
            //第0行是表名，忽略，从第二行开始读取
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                InterfaceInfo interfaceInfo = new InterfaceInfo();
                Cell cell = row.getCell(0);
                interfaceInfo.setName(getCellStringVal(cell).trim());
                cell = row.getCell(1);
                interfaceInfo.setAddress(getCellStringVal(cell).trim());
                cell = row.getCell(2);
                interfaceInfo.setData(getCellStringVal(cell).trim());
                contents.add(interfaceInfo);
            }
            return contents;
        } catch (Exception e) {
            throw e;
        } finally {
            IOUtils.closeQuietly(workbook);
        }
    }


    private static String getCellStringVal(Cell cell) {
        if(cell == null){
            return "";
        }
        CellType cellType = cell.getCellTypeEnum();
        switch (cellType) {
            case NUMERIC:
                return String.valueOf(cell.getNumericCellValue());
            case STRING:
                return cell.getStringCellValue();
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
            case ERROR:
                return String.valueOf(cell.getErrorCellValue());
            default:
                return null;
        }
    }
}
